﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using BaseData;
using BaseData.Common;
using TaxEntity.House;

namespace TaxData.House
{
    /// <summary>
    /// 房产信息
    /// </summary>
    public class HouseInfoData
    {
        /// <summary>
        /// 获取房产信息实体
        /// </summary>
        public HouseInfoEntity GetEntityById(long ID)
        {
            string sql = "select * from HouseInfo with(nolock) where ID = @ID and status=1";
            SqlParameter p = new SqlParameter("@ID", SqlDbType.BigInt, 16);
            p.Value = ID;
            DataTable dt = DataConfig.QueryRecords(BusinessType.DBRead, sql, p);
            HouseInfoEntity model = this.GetEntityHelper(dt);
            return model;
        }

        /// <summary>
        /// 获取房产信息实体
        /// </summary>
        public HouseInfoEntity GetEntityByCertificateID(string CertificateID)
        {
            string sql = "select * from HouseInfo with(nolock) where CertificateID = @CertificateID and status=1";
            SqlParameter p = new SqlParameter("@CertificateID", SqlDbType.VarChar, 25);
            p.Value = CertificateID;
            DataTable dt = DataConfig.QueryRecords(BusinessType.DBRead, sql, p);
            HouseInfoEntity model = this.GetEntityHelper(dt);
            return model;
        }

        /// <summary>
        /// 删除房产
        /// </summary>
        public bool Delete(long ID)
        {
            string sql = "update HouseInfo set status=0 where ID = @ID";
            SqlParameter p = new SqlParameter("@ID", SqlDbType.BigInt, 16);
            p.Value = ID;
            int affectRows = DataConfig.QueryNoRecords(BusinessType.DBWrite, sql, p);
            return affectRows > 0;
        }

        /// <summary>
        /// 获取房产列表
        /// </summary>
        public DataTable GetList(string strWhere, int pageSize, int page, ref int allCount)
        {
            DataTable dt = new DataTable();
            string condition = " where status=1" + strWhere;
            string sqlCount = "select count(1) from HouseInfo with(nolock) " + condition;
            allCount = (int)DataConfig.GetSingle(BusinessType.DBRead, sqlCount);
            if (allCount > 0)
            {
                int startNum = (page - 1) * pageSize + 1;
                int endNum = page * pageSize;
                string sqlInner = "select *,departmentname = (select top 1 Name from Department b with(nolock) where a.DepartmentID=b.ID),sName=(select top 1 sName from AdmUser b with(nolock) where a.ManagerID=b.UserId)"
                    + ",xiangzhenName=(select top 1 Name from Xiangzhen where ID = XiangzhenID)"
                    + ",jiedaoName=(select top 1 Name from Jiedao where ID = JiedaoID)"
                    + ",diduanname = (select top 1 name from DiDuan b with(nolock) where a.diduanid=b.id) "
                    + ",ROW_NUMBER() over(order by AddTime desc) as RowNumber from HouseInfo a with(nolock) " + condition;
                string sqlUserList = string.Format("SELECT * FROM ({0}) T WHERE T.RowNumber BETWEEN {1} AND {2} order by RowNumber asc", sqlInner, startNum, endNum);
                dt = DataConfig.QueryRecords(BusinessType.DBRead, sqlUserList);
            }
            return dt;
        }

        /// <summary>
        /// 增加房产
        /// </summary>
        /// <param name="model">房产信息实体</param>
        /// <returns>成功房产编号,失败返回0</returns>
        public long Add(HouseInfoEntity model)
        {
            StringBuilder strSql = new StringBuilder();
            StringBuilder strSql1 = new StringBuilder();
            StringBuilder strSql2 = new StringBuilder();
            if (model.CertificateID != null)
            {
                strSql1.Append("CertificateID,");
                strSql2.Append("'" + model.CertificateID + "',");
            }
            if (model.Name != null)
            {
                strSql1.Append("Name,");
                strSql2.Append("'" + model.Name + "',");
            }
            if (model.IDCardNum != null)
            {
                strSql1.Append("IDCardNum,");
                strSql2.Append("'" + model.IDCardNum + "',");
            }
            if (model.DepartmentID != null)
            {
                strSql1.Append("DepartmentID,");
                strSql2.Append("" + model.DepartmentID + ",");
            }
            if (model.ManagerID != null)
            {
                strSql1.Append("ManagerID,");
                strSql2.Append("" + model.ManagerID + ",");
            }
            if (model.CertificateDate != null)
            {
                strSql1.Append("CertificateDate,");
                strSql2.Append("'" + model.CertificateDate + "',");
            }
            if (model.YearLimit != null)
            {
                strSql1.Append("YearLimit,");
                strSql2.Append("" + model.YearLimit + ",");
            }
            if (model.BuildingNum != null)
            {
                strSql1.Append("BuildingNum,");
                strSql2.Append("'" + model.BuildingNum + "',");
            }
            if (model.XiangzhenID != null)
            {
                strSql1.Append("XiangzhenID,");
                strSql2.Append("" + model.XiangzhenID + ",");
            }
            if (model.JiedaoID != null)
            {
                strSql1.Append("JiedaoID,");
                strSql2.Append("" + model.JiedaoID + ",");
            }
            if (model.DiduanID != null)
            {
                strSql1.Append("DiduanID,");
                strSql2.Append("" + model.DiduanID + ",");
            }
            if (model.TudiArea != null)
            {
                strSql1.Append("TudiArea,");
                strSql2.Append("" + model.TudiArea + ",");
            }
            if (model.FangchanArea != null)
            {
                strSql1.Append("FangchanArea,");
                strSql2.Append("" + model.FangchanArea + ",");
            }
            if (model.OriginalPrice != null)
            {
                strSql1.Append("OriginalPrice,");
                strSql2.Append("" + model.OriginalPrice + ",");
            }
            if (model.RemainPrice != null)
            {
                strSql1.Append("RemainPrice,");
                strSql2.Append("" + model.RemainPrice + ",");
            }
            if (model.HouseType != null)
            {
                strSql1.Append("HouseType,");
                strSql2.Append("'" + model.HouseType + "',");
            }
            if (model.MobileCode != null)
            {
                strSql1.Append("MobileCode,");
                strSql2.Append("'" + model.MobileCode + "',");
            }
            if (model.Telephone != null)
            {
                strSql1.Append("Telephone,");
                strSql2.Append("'" + model.Telephone + "',");
            }
            if (model.Address != null)
            {
                strSql1.Append("Address,");
                strSql2.Append("'" + model.Address + "',");
            }
            if (model.Memo != null)
            {
                strSql1.Append("Memo,");
                strSql2.Append("'" + model.Memo + "',");
            }
            if (model.OptUserID != null)
            {
                strSql1.Append("OptUserID,");
                strSql2.Append("" + model.OptUserID + ",");
            }
            if (model.Status != null)
            {
                strSql1.Append("Status,");
                strSql2.Append("" + model.Status + ",");
            }
            if (model.AddTime != null)
            {
                strSql1.Append("AddTime,");
                strSql2.Append("'" + model.AddTime + "',");
            }
            if (model.ModifyTime != null)
            {
                strSql1.Append("ModifyTime,");
                strSql2.Append("'" + model.ModifyTime + "',");
            }
            strSql.Append("insert into HouseInfo(");
            strSql.Append(strSql1.ToString().Remove(strSql1.Length - 1));
            strSql.Append(")");
            strSql.Append(" values (");
            strSql.Append(strSql2.ToString().Remove(strSql2.Length - 1));
            strSql.Append(")");
            strSql.Append(";select @@IDENTITY");
            object obj = DataConfig.GetSingle(BusinessType.DBWrite, strSql.ToString());
            long ID = 0;
            if (obj != null && obj != DBNull.Value)
            {
                long.TryParse(obj.ToString(), out ID);
            }
            return ID;
        }

        /// <summary>
        /// 修改房产信息
        /// </summary>
        /// <param name="model">修改房产</param>
        /// <returns>操作结果</returns>
        public bool Update(HouseInfoEntity model)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("update HouseInfo set ");
            if (model.Name != null)
            {
                strSql.Append("Name='" + model.Name + "',");
            }
            if (model.IDCardNum != null)
            {
                strSql.Append("IDCardNum='" + model.IDCardNum + "',");
            }
            if (model.DepartmentID != null)
            {
                strSql.Append("DepartmentID=" + model.DepartmentID + ",");
            }
            if (model.ManagerID != null)
            {
                strSql.Append("ManagerID=" + model.ManagerID + ",");
            }
            if (model.CertificateDate != null)
            {
                strSql.Append("CertificateDate='" + model.CertificateDate + "',");
            }
            if (model.YearLimit != null)
            {
                strSql.Append("YearLimit=" + model.YearLimit + ",");
            }
            if (model.BuildingNum != null)
            {
                strSql.Append("BuildingNum='" + model.BuildingNum + "',");
            }
            if (model.XiangzhenID != null)
            {
                strSql.Append("XiangzhenID=" + model.XiangzhenID + ",");
            }
            if (model.JiedaoID != null)
            {
                strSql.Append("JiedaoID=" + model.JiedaoID + ",");
            }
            if (model.DiduanID != null)
            {
                strSql.Append("DiduanID=" + model.DiduanID + ",");
            }
            if (model.TudiArea != null)
            {
                strSql.Append("TudiArea=" + model.TudiArea + ",");
            }
            if (model.FangchanArea != null)
            {
                strSql.Append("FangchanArea=" + model.FangchanArea + ",");
            }
            if (model.OriginalPrice != null)
            {
                strSql.Append("OriginalPrice=" + model.OriginalPrice + ",");
            }
            if (model.RemainPrice != null)
            {
                strSql.Append("RemainPrice=" + model.RemainPrice + ",");
            }
            if (model.HouseType != null)
            {
                strSql.Append("HouseType='" + model.HouseType + "',");
            }
            if (model.MobileCode != null)
            {
                strSql.Append("MobileCode='" + model.MobileCode + "',");
            }
            if (model.Telephone != null)
            {
                strSql.Append("Telephone='" + model.Telephone + "',");
            }
            if (model.Address != null)
            {
                strSql.Append("Address='" + model.Address + "',");
            }
            if (model.Memo != null)
            {
                strSql.Append("Memo='" + model.Memo + "',");
            }
            if (model.OptUserID != null)
            {
                strSql.Append("OptUserID=" + model.OptUserID + ",");
            }
            if (model.Status != null)
            {
                strSql.Append("Status=" + model.Status + ",");
            }
            if (model.ModifyTime != null)
            {
                strSql.Append("ModifyTime='" + model.ModifyTime + "',");
            }
            int n = strSql.ToString().LastIndexOf(",");
            strSql.Remove(n, 1);
            strSql.Append(" where Id=" + model.Id + "");
            int rows = DataConfig.QueryNoRecords(BusinessType.DBWrite, strSql.ToString());
            return rows > 0;
        }

        /// <summary>
        /// 从DataTable中得到一个实体对象
        /// </summary>
        private HouseInfoEntity GetEntityHelper(DataTable dt)
        {
            HouseInfoEntity model = null;
            if (dt != null && dt.Rows.Count > 0)
            {
                model = new HouseInfoEntity();
                if (dt.Rows[0]["Id"] != null && dt.Rows[0]["Id"].ToString() != "")
                {
                    model.Id = long.Parse(dt.Rows[0]["Id"].ToString());
                }
                if (dt.Rows[0]["CertificateID"] != null && dt.Rows[0]["CertificateID"].ToString() != "")
                {
                    model.CertificateID = dt.Rows[0]["CertificateID"].ToString();
                }
                if (dt.Rows[0]["Name"] != null && dt.Rows[0]["Name"].ToString() != "")
                {
                    model.Name = dt.Rows[0]["Name"].ToString();
                }
                if (dt.Rows[0]["IDCardNum"] != null && dt.Rows[0]["IDCardNum"].ToString() != "")
                {
                    model.IDCardNum = dt.Rows[0]["IDCardNum"].ToString();
                }
                if (dt.Rows[0]["DepartmentID"] != null && dt.Rows[0]["DepartmentID"].ToString() != "")
                {
                    model.DepartmentID = int.Parse(dt.Rows[0]["DepartmentID"].ToString());
                }
                if (dt.Rows[0]["ManagerID"] != null && dt.Rows[0]["ManagerID"].ToString() != "")
                {
                    model.ManagerID = long.Parse(dt.Rows[0]["ManagerID"].ToString());
                }
                if (dt.Rows[0]["CertificateDate"] != null && dt.Rows[0]["CertificateDate"].ToString() != "")
                {
                    model.CertificateDate = DateTime.Parse(dt.Rows[0]["CertificateDate"].ToString());
                }
                if (dt.Rows[0]["YearLimit"] != null && dt.Rows[0]["YearLimit"].ToString() != "")
                {
                    model.YearLimit = int.Parse(dt.Rows[0]["YearLimit"].ToString());
                }
                if (dt.Rows[0]["BuildingNum"] != null && dt.Rows[0]["BuildingNum"].ToString() != "")
                {
                    model.BuildingNum = dt.Rows[0]["BuildingNum"].ToString();
                }
                if (dt.Rows[0]["XiangzhenID"] != null && dt.Rows[0]["XiangzhenID"].ToString() != "")
                {
                    model.XiangzhenID = int.Parse(dt.Rows[0]["XiangzhenID"].ToString());
                }
                if (dt.Rows[0]["JiedaoID"] != null && dt.Rows[0]["JiedaoID"].ToString() != "")
                {
                    model.JiedaoID = int.Parse(dt.Rows[0]["JiedaoID"].ToString());
                }
                if (dt.Rows[0]["DiduanID"] != null && dt.Rows[0]["DiduanID"].ToString() != "")
                {
                    model.DiduanID = int.Parse(dt.Rows[0]["DiduanID"].ToString());
                }
                if (dt.Rows[0]["TudiArea"] != null && dt.Rows[0]["TudiArea"].ToString() != "")
                {
                    model.TudiArea = decimal.Parse(dt.Rows[0]["TudiArea"].ToString());
                }
                if (dt.Rows[0]["FangchanArea"] != null && dt.Rows[0]["FangchanArea"].ToString() != "")
                {
                    model.FangchanArea = decimal.Parse(dt.Rows[0]["FangchanArea"].ToString());
                }
                if (dt.Rows[0]["OriginalPrice"] != null && dt.Rows[0]["OriginalPrice"].ToString() != "")
                {
                    model.OriginalPrice = decimal.Parse(dt.Rows[0]["OriginalPrice"].ToString());
                }
                if (dt.Rows[0]["RemainPrice"] != null && dt.Rows[0]["RemainPrice"].ToString() != "")
                {
                    model.RemainPrice = decimal.Parse(dt.Rows[0]["RemainPrice"].ToString());
                }
                if (dt.Rows[0]["HouseType"] != null && dt.Rows[0]["HouseType"].ToString() != "")
                {
                    model.HouseType = dt.Rows[0]["HouseType"].ToString();
                }
                if (dt.Rows[0]["MobileCode"] != null && dt.Rows[0]["MobileCode"].ToString() != "")
                {
                    model.MobileCode = dt.Rows[0]["MobileCode"].ToString();
                }
                if (dt.Rows[0]["Telephone"] != null && dt.Rows[0]["Telephone"].ToString() != "")
                {
                    model.Telephone = dt.Rows[0]["Telephone"].ToString();
                }
                if (dt.Rows[0]["Address"] != null && dt.Rows[0]["Address"].ToString() != "")
                {
                    model.Address = dt.Rows[0]["Address"].ToString();
                }
                if (dt.Rows[0]["Memo"] != null && dt.Rows[0]["Memo"].ToString() != "")
                {
                    model.Memo = dt.Rows[0]["Memo"].ToString();
                }
                if (dt.Rows[0]["OptUserID"] != null && dt.Rows[0]["OptUserID"].ToString() != "")
                {
                    model.OptUserID = long.Parse(dt.Rows[0]["OptUserID"].ToString());
                }
                if (dt.Rows[0]["Status"] != null && dt.Rows[0]["Status"].ToString() != "")
                {
                    model.Status = int.Parse(dt.Rows[0]["Status"].ToString());
                }
                if (dt.Rows[0]["AddTime"] != null && dt.Rows[0]["AddTime"].ToString() != "")
                {
                    model.AddTime = DateTime.Parse(dt.Rows[0]["AddTime"].ToString());
                }
                if (dt.Rows[0]["ModifyTime"] != null && dt.Rows[0]["ModifyTime"].ToString() != "")
                {
                    model.ModifyTime = DateTime.Parse(dt.Rows[0]["ModifyTime"].ToString());
                }
                return model;
            }
            return model;
        }

    }
}
